The .NET data provider factory pattern allows you to build a single code base using generalized data access types. Furthermore, using application configuration files (and the <connectionStrings> subelement), you can obtain providers and connection strings declaratively, without the need to recompile or redeploy the assembly that uses the ADO.NET APIs.
To understand the data provider factory implementation, recall from Table 21-1 that the classes within a data provider each derive from the same base classes defined within the System.Data.Common namespace:
Each of the Microsoft-supplied data providers contains a class type that derives from System.Data.Common.DbProviderFactory. This base class defines several methods that retrieve providerspecific data objects. Here is a snapshot of the relevant members of DbProviderFactory:
DbProviderFactory
{
...
public virtual DbCommand CreateCommand();
public virtual DbCommandBuilder CreateCommandBuilder();
public virtual DbConnection CreateConnection();
public virtual DbConnectionStringBuilder CreateConnectionStringBuilder();
public virtual DbDataAdapter CreateDataAdapter();
public virtual DbDataSourceEnumerator CreateDataSourceEnumerator();
public virtual DbParameter CreateParameter();
}
To obtain the DbProviderFactory-derived type for your data provider, the System.Data.Common namespace provides a class type named DbProviderFactories (note the plural in this type’s name). You can use the static GetFactory() method to obtain the specific DbProviderFactory object of the specified data provider; do this by specifying a string name that represents the .NET namespace containing the provider’s functionality:
static void Main(string[] args) { // Get the factory for the SQL data provider. DbProviderFactory sqlFactory = DbProviderFactories.GetFactory("System.Data.SqlClient"); ... }
Of course, rather than obtaining a factory using a hard-coded string literal, you could instead read in this information from a client-side *.config file (much like the earlier MyConnectionFactory example). You will learn how to do this shortly; for the moment, you can obtain the associated provider-specific data objects (e.g., connections, commands, and data readers) once you obtain the factory for your data provider.
Note For all practical purposes, you can regard the argument sent to DbProviderFactories.GetFactory() as the name of the data provider’s .NET namespace. In reality, the machine.config value uses this string value to load the correct library dynamically from the Global Assembly Cache.
For a complete example, you can create a new C# Console Application (named DataProviderFactory) that prints out the automobile inventory of the AutoLot database. For this initial example, you will hardcode the data access logic directly within the DataProviderFactory.exe assembly (to keep things simple for the time being). However, once you begin to dig into the details of the ADO.NET programming model, you will isolate your data logic to a specific .NET code library that you will use for the remainder of this book.
Begin by adding a reference to the System.Configuration.dll assembly and importing the System.Configuration namespace. Next, insert an App.config file to the current project and define an empty <appSettings> element. Add a new key-named provider that maps to the namespace name of the data provider you wish to obtain (System.Data.SqlClient). Also, define a connection string that represents a connection to the AutoLot database (on the local instance of SQL Server Express):
<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <!-- Which provider? --> <add key="provider" value="System.Data.SqlClient" /> <!-- Which connection string? --> <add key="cnStr" value= "Data Source=(local)\SQLEXPRESS; Initial Catalog=AutoLot;Integrated Security=True"/> </appSettings> </configuration>
Note You will learn about connection strings in more detail momentarily; however, if you select your AutoLot database icon within the Server Explorer, you can copy-and-paste the correct connection string from the Connection String property of the Visual Studio 2010 Properties Window.
Now that you have a proper *.config file, you can read in the provider and cnStr values using the ConfigurationManager.AppSettings indexer. The provider value will be passed to DbProviderFactories.GetFactory() to obtain the data provider-specific factory type. You will use the cnStr value to set the ConnectionString property of the DbConnection-derived type.
Assuming you have imported the System.Data and System.Data.Common namespaces, you can update your Main() method like this:
static void Main(string[] args) { Console.WriteLine("***** Fun with Data Provider Factories *****\n"); // Get Connection string/provider from *.config. string dp = ConfigurationManager.AppSettings["provider"]; string cnStr = ConfigurationManager.AppSettings["cnStr"]; // Get the factory provider. DbProviderFactory df = DbProviderFactories.GetFactory(dp); // Now get the connection object. using (DbConnection cn = df.CreateConnection()) { Console.WriteLine("Your connection object is a: {0}", cn.GetType().Name); cn.ConnectionString = cnStr; cn.Open(); // Make command object. DbCommand cmd = df.CreateCommand(); Console.WriteLine("Your command object is a: {0}", cmd.GetType().Name); cmd.Connection = cn; cmd.CommandText = "Select * From Inventory"; // Print out data with data reader. using (DbDataReader dr = cmd.ExecuteReader()) { Console.WriteLine("Your data reader object is a: {0}", dr.GetType().Name); Console.WriteLine("\n***** Current Inventory *****"); while (dr.Read()) Console.WriteLine("-> Car #{0} is a {1}.", dr["CarID"], dr["Make"].ToString()); } } Console.ReadLine(); }
Notice that, for diagnostic purposes, you use reflection services to print the name of the underlying connection, command, and data reader. If you run this application, you will find the current data in the Inventory table of the AutoLot database printed to the console:
***** Fun with Data Provider Factories ***** Your connection object is a: SqlConnection Your command object is a: SqlCommand Your data reader object is a: SqlDataReader ***** Current Inventory ***** -> Car #83 is a Ford. -> Car #107 is a Ford. -> Car #678 is a Yugo. -> Car #904 is a VW. -> Car #1000 is a BMW. -> Car #1001 is a BMW. -> Car #1992 is a Saab.
Now change the *.config file to specify System.Data.OleDb as the data provider (and update your connection string with a Provider segment):
<configuration> <appSettings> <!-- Which provider? --> <add key="provider" value="System.Data.OleDb" /> <!-- Which connection string? --> <add key="cnStr" value= "Provider=SQLOLEDB;Data Source=(local)\SQLEXPRESS; Integrated Security=SSPI;Initial Catalog=AutoLot"/> </appSettings> </configuration>
Doing this indicates that the System.Data.OleDb types are used behind the scenes:
***** Fun with Data Provider Factories ***** Your connection object is a: OleDbConnection Your command object is a: OleDbCommand Your data reader object is a: OleDbDataReader ***** Current Inventory ***** -> Car #83 is a Ford. -> Car #107 is a Ford. -> Car #678 is a Yugo. -> Car #904 is a VW. -> Car #1000 is a BMW. -> Car #1001 is a BMW. -> Car #1992 is a Saab.
Of course, based on your experience with ADO.NET, you might be a bit unsure exactly what the connection, command, and data reader objects actually do. Don’t sweat the details for the time being (quite a few pages remain in this chapter, after all!). At this point, it’s enough to know that you can use the ADO.NET data provider factory model to build a single code base that can consume various data providers in a declarative manner.
Although this is a powerful model, you must make sure that the code base uses only types and methods common to all providers through the members of the abstract base classes. Therefore, when authoring your code base, you are limited to the members exposed by DbConnection, DbCommand, and the other types of the System.Data.Common namespace.
Given this, you might find that this generalized approach prevents you from directly accessing some of the bells and whistles of a particular DBMS. If you must be able to invoke specific members of the underlying provider (e.g., SqlConnection), you can do so using an explicit cast, as in this example:
using (DbConnection cn = df.CreateConnection()) { Console.WriteLine("Your connection object is a: {0}", cn.GetType().Name); cn.ConnectionString = cnStr; cn.Open(); if (cn is SqlConnection) { // Print out which version of SQL Server is used. Console.WriteLine(((SqlConnection)cn).ServerVersion); } ... }
When doing this, however, your code base becomes a bit harder to maintain (and less flexible) because you must add a number of runtime checks. Nevertheless, if you need to build data access libraries in the most flexible way possible, the data provider factory model provides a great mechanism for doing so.
Currently your connection string data is in the <appSettings> element of your *.config file. Application configuration files might define an element named <connectionStrings>. Within this element, you can define any number of name/value pairs that can be programmatically read into memory using the ConfigurationManager.ConnectionStrings indexer. One advantage of this approach (as opposed to using the <appSettings> element and the ConfigurationManager.AppSettings indexer) is that you can define multiple connection strings for a single application in a consistent manner.
To see this in action, update your current App.config file as follows (note that each connection string is documented using the name and connectionString attributes rather than the key and value attributes you find in <appSettings>):
<configuration> <appSettings> <!-- Which provider? --> <add key="provider" value="System.Data.SqlClient" /> </appSettings> <!-- Here are the connection strings --> <connectionStrings> <add name ="AutoLotSqlProvider" connectionString = "Data Source=(local)\SQLEXPRESS; Integrated Security=SSPI;Initial Catalog=AutoLot"/> <add name ="AutoLotOleDbProvider" connectionString = "Provider=SQLOLEDB;Data Source=(local)\SQLEXPRESS; Integrated Security=SSPI;Initial Catalog=AutoLot"/> </connectionStrings> </configuration>
You can now update your Main() method as follows:
static void Main(string[] args) { Console.WriteLine("***** Fun with Data Provider Factories *****\n"); string dp = ConfigurationManager.AppSettings["provider"]; string cnStr = ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString; ... }
At this point, you have an application that can display the results of the Inventory table of the AutoLot database using a neutral code base. Offloading the provider name and connection string to an external *.config file, means that the data provider factory model can dynamically load the correct provider in the background. With this first example behind you, you can now dive into the details of working with the connected layer of ADO.NET.
Note Now that you understand the role of ADO.NET data provider factories, the remaining examples in this book will focus on the task at hand by explicitly using the types within the System.Data.SqlClient namespace. If you use a different database management system (such as Oracle), you need to update your code base accordingly.
Source Code You can find the DataProviderFactory project under the Chapter 21 subdirectory.